﻿Imports MySql.Data.MySqlClient
Public Class frmrpt_bl1
    Dim mysql As MySqlConnection = main_form.mysqlconection
    Dim mysql1 As MySqlConnection = main_form.mysqlconection1
    Dim mySqlCommand As New MySqlCommand
    Dim mySqlAdaptor As New MySqlDataAdapter
    Dim mySqlReader As MySqlDataReader

    Dim mySqlCommand1 As New MySqlCommand
    Dim mySqlAdaptor1 As New MySqlDataAdapter
    Dim mySqlReader1 As MySqlDataReader

    Dim container As String = ""
    Dim rpt1 As New rpt_BL1
    Dim rpt2 As New rpt_bl1_2

    Private Sub frmrpt_bl1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US")
        System.Threading.Thread.CurrentThread.CurrentUICulture = System.Threading.Thread.CurrentThread.CurrentCulture

        If frmset_bl.page1 = "0" Then

            CrystalReportViewer1.ReportSource = rpt1

            CrystalReportViewer1.Refresh()
            rpt1.SetParameterValue("textmessage", frmset_bl.textmsg)
            If frmset_bl.mark = "0" Then
                rpt1.SetParameterValue("menuctn", "Marks & Nos." + Environment.NewLine + "Container / Seal No.")
            Else
                rpt1.SetParameterValue("menuctn", "Container / Seal No.")

            End If

            If frmset_bl.meast = "0" Then
                rpt1.SetParameterValue("meas", "Don't Show")
            ElseIf frmset_bl.meast = "1" Then
                rpt1.SetParameterValue("meas", frmset_bl.meastext)
            End If
            mysql.Close()
            If mysql.State = ConnectionState.Closed Then
                mysql.Open()
            End If
            mySqlCommand.CommandText = "Select * from booking_bl join booking on booking_bl.idbooking_bl = booking.BINV join voyage on voyage.VOYAGEID = booking.BVOYAGE  left join consignee on  consignee.consigneeid = booking_bl.conid left join shipper on shipper.SHIPPERID = booking.BSHIP  where idbooking_bl ='" & frmset_bl.invoice & "';"
            ' mySqlCommand.CommandText = 
            mySqlCommand.Connection = mysql
            mySqlAdaptor.SelectCommand = mySqlCommand

            Try
                mySqlReader = mySqlCommand.ExecuteReader

                While (mySqlReader.Read())
                    rpt1.SetParameterValue("bookingno", mySqlReader("BNO"))
                    rpt1.SetParameterValue("billoflanding", mySqlReader("BLANDNO"))
                    rpt1.SetParameterValue("notify", mySqlReader("BNOTIFY"))
                    rpt1.SetParameterValue("shipname", mySqlReader("BSHIPNAME"))
                    rpt1.SetParameterValue("shipaddress", mySqlReader("SHIPADD"))
                    If mySqlReader("SHIPTELL").ToString.Length > 3 Then
                        rpt1.SetParameterValue("shiptell", "TELL : " + mySqlReader("SHIPTELL"))
                    Else
                        rpt1.SetParameterValue("shiptell", " ")
                    End If


                    If mySqlReader("shipfax").ToString.Length > 3 Then
                        rpt1.SetParameterValue("shipfax", "FAX : " + mySqlReader("SHIPFAX"))
                    Else
                        rpt1.SetParameterValue("shipfax", " ")
                    End If


                    If mySqlReader("CONNAME") Is DBNull.Value Then
                        rpt1.SetParameterValue("conname", " ")
                    Else
                        rpt1.SetParameterValue("conname", mySqlReader("CONNAME"))

                    End If
                    If mySqlReader("CONADD") Is DBNull.Value Then
                        rpt1.SetParameterValue("conaddress", " ")
                    Else
                        rpt1.SetParameterValue("conaddress", mySqlReader("CONADD"))
                    End If


                    If mySqlReader("CONTELL").ToString.Length > 3 Then
                        rpt1.SetParameterValue("contell", "TELL : " + mySqlReader("CONTELL"))

                    Else
                        rpt1.SetParameterValue("contell", " ")
                    End If


                    If mySqlReader("CONFAX").ToString.Length > 3 Then
                        rpt1.SetParameterValue("confax", "FAX : " + mySqlReader("CONFAX"))
                    Else
                        rpt1.SetParameterValue("confax", " ")
                    End If


                    If mySqlReader("BTYPEMOVE") = "0" Then
                        rpt1.SetParameterValue("typemove", "X")
                        rpt1.SetParameterValue("typemove1", "")
                    Else
                        rpt1.SetParameterValue("typemove1", "X")
                        rpt1.SetParameterValue("typemove", "")
                    End If

                    rpt1.SetParameterValue("vesselname", mySqlReader("VOYVESNAMES"))
                    If mySqlReader("VOYAGEID") = "3621" Then
                        rpt1.SetParameterValue("voyage", "S362.1")
                    Else
                        rpt1.SetParameterValue("voyage", "S" + Format(mySqlReader("VOYAGEID"), "000"))
                    End If


                    If mySqlReader("place") Is DBNull.Value Then
                        rpt1.SetParameterValue("placerecieve", "")

                    Else

                        rpt1.SetParameterValue("placerecieve", mySqlReader("place"))
                        'rpt1.SetParameterValue("placerecieve", "test1")


                    End If
                    rpt1.SetParameterValue("portdischarge", "PENANG,MALAYSIA")
                    rpt1.SetParameterValue("description", mySqlReader("BDESCRIPT"))
                    rpt1.SetParameterValue("containerno", mySqlReader("BCTNNO"))
                    rpt1.SetParameterValue("grossweight", mySqlReader("BGROSS"))
                    rpt1.SetParameterValue("number_font", mySqlReader("number_word"))
                    rpt1.SetParameterValue("number", mySqlReader("number"))
                    rpt1.SetParameterValue("typectn", mySqlReader("BCTNTYPE"))


                    If frmset_bl.checkinv = "0" Then
                        If mySqlReader("billbooking") Is DBNull.Value Then
                            rpt1.SetParameterValue("invoicenum", "")
                        Else

                            rpt1.SetParameterValue("invoicenum", "***INV NO." + mySqlReader("billbooking"))
                        End If

                    Else
                        rpt1.SetParameterValue("invoicenum", "")

                    End If
                    'rpt1.SetParameterValue("invoicenum", "***INV NO." + frmset_bl.invoice)
                    rpt1.SetParameterValue("date_commit", frmset_bl.datetimebl)
                End While
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try
            If frmset_bl.nm = "0" Then
                container = "N/M" + Environment.NewLine
            ElseIf frmset_bl.nm = "2" Then
                container = "FLEXIONE" + Environment.NewLine
            Else
                container = ""
            End If

            mysql.Close()
            If mysql.State = ConnectionState.Closed Then
                mysql.Open()
            End If
            mySqlCommand.CommandText = "Select * from booking join borrow on booking.BOOKINGID = borrow.BOOKID join ctnmain on ctnmain.CTNMAINID = borrow.CTNID    where BOOKINGID ='" & frmset_bl.idbooking & "' ;"
            ' mySqlCommand.CommandText = 
            mySqlCommand.Connection = mysql
            mySqlAdaptor.SelectCommand = mySqlCommand

            Try
                mySqlReader = mySqlCommand.ExecuteReader

                While (mySqlReader.Read())
                    container += mySqlReader("CTNSTRING") + "/" + mySqlReader("CTNSEALID") + Environment.NewLine
                End While
            Catch ex As Exception

            End Try
            rpt1.SetParameterValue("container", container)

        Else
            CrystalReportViewer1.ReportSource = rpt2

            CrystalReportViewer1.Refresh()
            rpt1.SetParameterValue("textmessage", frmset_bl.textmsg)
            If frmset_bl.mark = "0" Then
                rpt2.SetParameterValue("menuctn", "Marks & Nos." + Environment.NewLine + "Container / Seal No.")
            Else
                rpt2.SetParameterValue("menuctn", "Container / Seal No.")

            End If
            mysql.Close()

            If frmset_bl.meast = "0" Then
                rpt2.SetParameterValue("meas", "Don't Show")
            ElseIf frmset_bl.meast = "1" Then
                rpt2.SetParameterValue("meas", frmset_bl.meastext)
            End If

            If mysql.State = ConnectionState.Closed Then
                mysql.Open()
            End If
            mySqlCommand.CommandText = "Select * from booking_bl join booking on booking_bl.idbooking_bl = booking.BINV join voyage on voyage.VOYAGEID = booking.BVOYAGE  left join consignee on  consignee.consigneeid = booking_bl.conid left join shipper on shipper.SHIPPERID = booking.BSHIP  where idbooking_bl ='" & frmset_bl.invoice & "';"
            ' mySqlCommand.CommandText = 
            mySqlCommand.Connection = mysql
            mySqlAdaptor.SelectCommand = mySqlCommand

            Try
                mySqlReader = mySqlCommand.ExecuteReader

                While (mySqlReader.Read())
                    rpt2.SetParameterValue("bookingno", mySqlReader("BNO"))
                    rpt2.SetParameterValue("billoflanding", mySqlReader("BLANDNO"))
                    rpt2.SetParameterValue("notify", mySqlReader("BNOTIFY"))
                    rpt2.SetParameterValue("shipname", mySqlReader("BSHIPNAME"))
                    rpt2.SetParameterValue("shipaddress", mySqlReader("SHIPADD"))
                    If mySqlReader("SHIPTELL").ToString.Length > 3 Then
                        rpt2.SetParameterValue("shiptell", "TELL : " + mySqlReader("SHIPTELL"))
                    Else
                        rpt2.SetParameterValue("shiptell", " ")
                    End If


                    If mySqlReader("shipfax").ToString.Length > 3 Then
                        rpt2.SetParameterValue("shipfax", "FAX : " + mySqlReader("SHIPFAX"))
                    Else
                        rpt2.SetParameterValue("shipfax", " ")
                    End If


                    rpt2.SetParameterValue("conname", mySqlReader("CONNAME"))
                    rpt2.SetParameterValue("conaddress", mySqlReader("CONADD"))

                    If mySqlReader("CONTELL").ToString.Length > 3 Then
                        rpt2.SetParameterValue("contell", "TELL : " + mySqlReader("CONTELL"))

                    Else
                        rpt2.SetParameterValue("contell", " ")
                    End If


                    If mySqlReader("CONFAX").ToString.Length > 3 Then
                        rpt2.SetParameterValue("confax", "FAX : " + mySqlReader("CONFAX"))
                    Else
                        rpt2.SetParameterValue("confax", " ")
                    End If



                    If mySqlReader("BTYPEMOVE") = "0" Then
                        rpt2.SetParameterValue("typemove", "X")
                        rpt2.SetParameterValue("typemove1", "")
                    Else
                        rpt2.SetParameterValue("typemove1", "X")
                        rpt2.SetParameterValue("typemove", "")
                    End If

                    rpt2.SetParameterValue("vesselname", mySqlReader("VOYVESNAMES"))
                    If mySqlReader("VOYAGEID") = "3621" Then
                        rpt2.SetParameterValue("voyage", "S362.1")
                    Else
                        rpt2.SetParameterValue("voyage", "S" + Format(mySqlReader("VOYAGEID"), "000"))
                    End If

                    If mySqlReader("place") Is DBNull.Value Then
                        rpt2.SetParameterValue("placerecieve", "")

                    Else

                        rpt2.SetParameterValue("placerecieve", mySqlReader("place"))
                        'rpt1.SetParameterValue("placerecieve", "test1")


                    End If
                    rpt2.SetParameterValue("portdischarge", "PENANG,MALAYSIA")
                    rpt2.SetParameterValue("description", mySqlReader("BDESCRIPT"))
                    rpt2.SetParameterValue("containerno", mySqlReader("BCTNNO"))
                    rpt2.SetParameterValue("grossweight", mySqlReader("BGROSS"))
                    rpt2.SetParameterValue("number_font", mySqlReader("number_word"))
                    rpt2.SetParameterValue("number", mySqlReader("number"))
                    rpt2.SetParameterValue("typectn", mySqlReader("BCTNTYPE"))

                    If frmset_bl.checkinv = "0" Then
                        If mySqlReader("billbooking") Is DBNull.Value Then
                        Else

                            rpt2.SetParameterValue("invoicenum", "***INV NO." + mySqlReader("billbooking"))
                        End If

                    Else
                        rpt2.SetParameterValue("invoicenum", "")

                    End If
                    'rpt1.SetParameterValue("invoicenum", "***INV NO." + frmset_bl.invoice)
                    rpt2.SetParameterValue("date_commit", frmset_bl.datetimebl)
                End While
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try
            If frmset_bl.nm = "0" Then
                rpt2.SetParameterValue("nm", "N/M" + Environment.NewLine + "TO BE CONTINUED ON ATTACHED LIST")
            ElseIf frmset_bl.nm = 2 Then
                rpt2.SetParameterValue("nm", "FLEXIONE" + Environment.NewLine + "TO BE CONTINUED ON ATTACHED LIST")
            Else
                rpt2.SetParameterValue("nm", "TO BE CONTINUED ON ATTACHED LIST")

            End If

            rpt2.SetParameterValue("portdischarge", "PENANG,MALAYSIA")

            Dim count_line As Integer = 1
            Dim countstring As String = ""
            mysql.Close()
            If mysql.State = ConnectionState.Closed Then
                mysql.Open()
            End If
            mySqlCommand.CommandText = "Select * from booking join borrow on booking.BOOKINGID = borrow.BOOKID join ctnmain on ctnmain.CTNMAINID = borrow.CTNID    where BOOKINGID ='" & frmset_bl.idbooking & "';"
            ' mySqlCommand.CommandText = 
            mySqlCommand.Connection = mysql
            mySqlAdaptor.SelectCommand = mySqlCommand

            Try
                mySqlReader = mySqlCommand.ExecuteReader

                While (mySqlReader.Read())
                    container += mySqlReader("CTNSTRING") + "/" + mySqlReader("CTNSEALID") + Environment.NewLine
                    countstring += count_line.ToString + "." + Environment.NewLine
                    count_line += 1
                End While
            Catch ex As Exception

            End Try
            rpt2.SetParameterValue("container", container)
            rpt2.SetParameterValue("count", countstring)

        End If


    End Sub
End Class